--创建数据库
CREATE database ETRMS

-- Admin 管理员信息表
CREATE TABLE Admin
(
	adminId varchar(20) not null primary key,
	passWd char(32) not null,
	name varchar(20) not null,
	sex char(2),
	age int ,
	idNumber char(18) not null UNIQUE,
	tel char(11) not null,
	workDate datetime ,
	Apower int not null
);
-- classInfo 教室信息表

CREATE TABLE classInfo
(
	classId varchar(10) not null primary key,
	setAmount int not null,
	used int not null,
	empty int not null
	
);
-- registerInfo 登记表

CREATE TABLE registerInfo
(
	regId integer PRIMARY KEY autoincrement,
	seatId char(6) not null,
	stuId varchar(13) not null,
	regDate datetime not null 
);
-- seatInfo 座位表

CREATE TABLE seatInfo
(
	seatId varchar(10) not null primary key,
	classId varchar(10) not null,
	seatNum varchar(10) not null,
	status int check(status ='1' or status ='0') not null 
);
-- signInfo 签到表

CREATE TABLE signInfo
(
	signId integer PRIMARY KEY autoincrement,
	regId varchar(20) not null,
	seriesDays	int,
	lastSign datetime
);
-- stuInfo 学生信息表

CREATE TABLE stuInfo
(
	num int NOT NULL,
	stuId varchar(13)  NOT NULL,
	major nvarchar(50) NOT NULL,
	class vvarchar(50)  NOT NULL,
	name varchar(20)  NOT NULL,
	sex varchar(2)  NOT NULL,
	idNumber char(18) not null,
	address nvarchar(1024)  NULL,
	stuTel char(11)  NOT NULL,
	stuQQ varchar(10)  NULL,
	pName varchar(20)  NULL,
	pTel char(11)  NULL,
	dormitory varchar(10)  NOT NULL,
	room varchar(10)  NOT NULL,
	remake nvarchar(1024)  NULL
);
--------------------------------------------------------
alter table registerInfo drop column regId;--删除列
alter table registerInfo add regId int PRIMARY KEY identity(1,1);--修改列为自增

--创建表
DROP table registerInfo;
CREATE TABLE registerInfo (
	regId int PRIMARY KEY identity(1,1) NOT NULL,
	seatId varchar(10) COLLATE Chinese_PRC_CI_AS NOT NULL,
	stuId varchar(13) COLLATE Chinese_PRC_CI_AS NOT NULL,
	regDate datetime NOT NULL,
) ;
--插入数据
INSERT INTO registerInfo
(seatId, stuId, regDate)
VALUES('102001', '2017001', '2017-08-09 21:37:00.000');
INSERT INTO registerInfo
(seatId, stuId, regDate)
VALUES('102002', '2017002', '2017-08-09 21:37:00.000');
INSERT INTO registerInfo
(seatId, stuId, regDate)
VALUES('102003', '2017003', '2017-08-09 21:37:00.000');

--------------------------------------------------------------------
DROP proc PRO_SeatReg;
--创建位置登记的存储过程
--注意：涉及到三表更新操作，位置登记，向registerinfo表插入数据，更新位置信息表，同时更新教室位置数量
Create proc PRO_SeatReg
	@seatId varchar(10),--位置id
    @stuId char(13),--学号
    @regDate datetime,--注册时间
  	@rtu int output, --返回执行状态
  	@rtuSeatId varchar(10) output --返回学生申请的所有位置id
as
declare @SeatNum int,@used int,@empty int,@classId int
	--查询学生是否有已登记扥位置
	select @SeatNum=COUNT(seatId) from registerInfo where stuId = @stuId
    if(@SeatNum >0)
    	begin 	
	    	select @rtuSeatId=seatId from registerInfo where stuId = @stuId--将位置ID赋给变量rtuSeatId
--        	DELETE FROM registerInfo where SeatId = @rtuSeatId--注销该同学位置       	
--        	UPDATE seatInfo set status = 0 WHERE seatId = @rtuSeatId--更新位置状态，0表示无人暂用，1表示已被占用
--        	select @classId=classId from seatInfo where seatId = @seatId
--			select  @empty=COUNT(status)-SUM(status),@used=SUM(status) FROM seatInfo where classId = @classId
--			UPDATE classInfo set used = @used ,empty =  @empty where classId = @classId
        	set @rtu = 0 --向前端反馈存储过程执行状态，方便QT调用
        end
    else
        begin
	    	insert into registerInfo(seatId,stuId,regDate) values(@seatId,@stuId,@regDate)
			UPDATE seatInfo set status = 1 WHERE seatId = @seatId--没有登记等允许该同学登记，同时更新位置信息表，同时统计位置数量将其数值同步到教室信息表
			select @classId=classId from seatInfo where seatId = @seatId
			select  @empty=COUNT(status)-SUM(status),@used=SUM(status) FROM seatInfo where classId = @classId
			UPDATE classInfo set used = @used ,empty =  @empty where classId = @classId
			set @rtu = 1
        end

declare @reout int,@reseat varchar(10)
EXEC PRO_SeatReg '102001','2017001','2021-04-21 19:55:40',@reout OUTPUT,@reseat OUTPUT 
PRINT @reout
PRINT @reseat
--------------------------------------------------------------
--1.通过座位号和教室id在位置信息表查询位置编号，
--2.通过位置编号在登记表查询学生id
--3.再通过学生id在学生信息表查询基本信息
--该功能用在点击座位按钮时能查看到登记着基本信息
DROP proc PRO_RegisterInfo;
--研修室模块，位置资源面板获取登记信息的存储过程
Create proc PRO_RegisterInfo
@varClassId varchar(10),
@VarSeatNum varchar(10)
AS
	select stuId,name,sex,major,class from stuInfo where stuId = (
		select stuId from registerInfo where seatId = (
			select seatId from seatInfo where classId = @varClassId and seatNum = @VarSeatNum
		)
	)
	
EXEC PRO_RegisterInfo 102,001


----------------------------------------------------------------
DROP proc PRO_QuerySeatInfo;
-- 查询位置位置（占用状态）信息
Create proc PRO_QuerySeatInfo
@varClassId varchar(10)
AS
select status from seatInfo where classId = @varClassId

EXEC PRO_QuerySeatInfo 102
------------------------------------------------------------------
--创建索引，默认是NONCLUSTERED非聚集索引，按ASE升序排列
CREATE INDEX INDEX_SeatInfo ON seatInfo(classId,seatNum)
CREATE INDEX INDEX_stuInfo ON stuInfo(stuId)

--查看索引
--1.查看表中定义的信息
EXEC sp_helpindex 'seatInfo'
EXEC sp_helpindex 'stuInfo'
--2.查看指定索引信息
DBCC show_statistics('seatInfo',PK__seatInfo__BC5329EAD0709A94)

--删除索引
DROP INDEX INDEX_stuInfo ON stuInfo
-----------------------------------------------------
select * from registerInfo
SELECT name,stuId,sex FROM stuInfo WHERE idNumber = '学生信息表中的身份证号'

---------------------------------------------------------------------------------------------------
SELECT    DATEDIFF(DAY, '2021-04-21 19:55:40', '2021-04-25 19:55:40')   AS 时间差距
-----------------------------------------------------
SELECT setAmount,used,empty from classInfo where classId = 102 ---获取教室信息表资源
------------------------------------------------------------------------------------------------------------------------------------------------
--查询已登记的学生基本信息
DROP proc PRO_GetBaseRegInfo
--登记管理模块，通过学号（学号，学号，学号）获取基本登记信息那部分
CREATE proc PRO_GetBaseRegInfo
@Id varchar(13)
as
SELECT regId,name,sex,stuTel,major,seatId,regDate FROM stuInfo right join registerInfo on stuInfo.stuId = registerInfo.stuId where registerInfo.stuId = @Id


EXEC PRO_GetBaseRegInfo '2017001'

-------------------------------------------------------------------------------------------------------------------- 
-- 注销位置
--学生学号->位置id->教室座位号
SELECT regId,seatId FROM registerInfo where stuId = '2017001'--查询登记id，座位id

DELETE FROM registerInfo where regId = '49'--使用登记id删除座位登记信息

UPDATE seatInfo set status = 0 WHERE seatId = '102001'  --新位置信息表

declare @used int,@empty int,@classId varchar(10)
set @classId = '102'
	select  @empty=COUNT(status)-SUM(status),@used=SUM(status) FROM seatInfo where classId = @classId
	UPDATE classInfo set used = @used ,empty =  @empty where classId = @classId--通过位置id查询教室id 
-------------------------------------------------
DROP PROC RmoveReg;
-- 注销位置存储过程
CREATE PROCEDURE RmoveReg
@stuId char(13)
AS
	    declare @regId int,@SeatID varchar(10),@used int,@empty int,@classId varchar(10)
		SELECT @regId=regId,@SeatID=seatId FROM registerInfo where stuId = @stuId--查询登记id，座位id
		DELETE FROM registerInfo where regId = @regId--使用登记id删除座位登记信息
		UPDATE seatInfo set status = 0 WHERE seatId = @SeatID  --更新位置信息表
		SELECT @classId=classId FROM seatInfo where seatId = @SeatID--通过位置id查询教室id
		select  @empty=COUNT(status)+SUM(status),@used=SUM(status) FROM seatInfo where classId = @classId
		UPDATE classInfo set used = @used ,empty =  @empty where classId = @classId--通过位置id查询教室id 
				
EXEC RmoveReg '学生的具体学号'
select COUNT(seatId) from registerInfo where stuId = '学生的具体学号'
    
EXEC PRO_GetBaseRegInfo '学生的具体学号'
-------------------------------------------------------------------------------------------------------------------- 
DROP proc PROC_Login
-- 登录存储过程
CREATE proc PROC_Login
@id varchar(20),@pwd varchar(15),@Status int out
as
	declare @Num int
	SELECT @Num=COUNT(*) FROM Admin where adminId = @id AND passWd = substring(sys.fn_sqlvarbasetostr(HashBytes('MD5',@pwd)),3,32)
	IF @Num = 1
	BEGIN 
		SET @Status = 1
	END
	ELSE 
	BEGIN 
		SET @Status = 0
	END
	PRINT @Num

exec PROC_Login ChenX,'123',OUT 
------------------------------------------ 
SELECT passWd FROM Admin where adminId = 'ChenX'

PRINT substring(sys.fn_sqlvarbasetostr(HashBytes('MD5','123')),3,32)-- SqlServer MD5加密

UPDATE Admin SET passWd = substring(sys.fn_sqlvarbasetostr(HashBytes('MD5','123')),3,32), name = 'ChenXi',sex = '男',age = 22,idNumber='学生信息表中的身份证号',tel='学生信息表中的电话号码',Apower = 0 WHERE adminId = 'ChenX'


declare @md5 char(32),@id varchar(20),@pwd varchar(15);
	set @id='ChenX';
	set @pwd='123';
	
	set @md5 = substring(sys.fn_sqlvarbasetostr(HashBytes('MD5',@pwd)),3,32);
	SELECT * FROM Admin where adminId = @id AND passWd = @md5;
	PRINT @md5;

-----------------------------------------------------------------


select COUNT(seatId) from registerInfo where stuId = '102001'

select  COUNT(status)-SUM(status),SUM(status) FROM seatInfo where classId = '102'


-----------------------------------------------------------------
DROP PROC PRO_GetSeatInfo;
-- 登记模块，学生登记，通过位置id查询已登记学生信息
create PROC PRO_GetSeatInfo
@Id varchar(10)
as
SELECT regId,name,sex,stuTel,major,seatId,regDate FROM stuInfo right join registerInfo on stuInfo.stuId = registerInfo.stuId where registerInfo.seatId = @Id

EXEC PRO_GetSeatInfo '102001'



    
    
    
